package sf.dsl.example;

import sf.database.dialect.DBDialect;
import sf.database.jdbc.sql.SQLContext;
import sf.database.jdbc.sql.SQLParameter;
import sf.database.util.SimpleSQLTemplate;
import sf.spring.util.CollectionUtils;
import sf.tools.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicBoolean;

public class ExampleSQL {
    /**
     * @param sb
     * @param list
     * @param example
     */
    public static void getColumns(StringBuilder sb, List<SQLParameter> list, Example example) {
        List<SimpleField> columns = example.getSelectColumns();
        if (CollectionUtils.isNotEmpty(columns)) {
            boolean f = false;
            for (SimpleField field : columns) {
                if (f) {
                    sb.append(',');
                }
                f = true;
                field.toSelectSql(sb, list, example.mustTableAlias, example.dialect);
            }
        }
//        else if(example.countColumn!=null){
//            Map<DBField, ColumnMapping> schemaMap = example.table.getSchemaMap();
//            sb.append(" count(").append(example.dialect.wrapKeyword(schemaMap.get(example.countColumn).rawColumnName)).append(") ");
//        }
    }

    /**
     * @param sb
     * @param list
     * @param example
     */
    public static void getCountColumn(StringBuilder sb, List<SQLParameter> list, Example example) {
        if (example.countColumn != null) {
            sb.append(" count(");
            example.countColumn.toConditionSql(sb, list, example.mustTableAlias, true, example.dialect);
            sb.append(')');
        }
    }

    /**
     * 获取完整的查询
     * @param example
     * @param dynamicTableName 限制,dynamicTableName只对单表有效,多表请勿使用,会产生意想不到的错误.
     * @param opt              查询操作
     * @param offsetLimit      是否使用offsetLimit
     * @return
     */
    public static SQLContext getSelectSQLContext(Example example, String dynamicTableName, SelectOpt opt, boolean offsetLimit) {
        example.hasMustTableAlias();
        setDynamicTableName(example, dynamicTableName);
        StringBuilder sql = new StringBuilder();
        List<SQLParameter> list = new ArrayList<>();
        SQLContext sqlContext = new SQLContext();
        sqlContext.setParas(list);

        example.sqlFlag.toSql(SQLFlag.Position.START, sql, list);

        getWithClause(sql, list, example);
        getSelectClause(sql, list, example, opt);

        StringBuilder temp = new StringBuilder();
        getFromClause(temp, list, example);
        String fromClause = temp.toString();
        if (StringUtils.isNotBlank(fromClause)) {
            sql.append(" from ").append(fromClause);
        }

        temp.delete(0, temp.length());
        getSQLContextWhereClause(temp, list, example);
        String whereClause = temp.toString();
        if (StringUtils.isNotBlank(whereClause)) {
            sql.append(" where ").append(whereClause);
        }

        temp.delete(0, temp.length());
        getGroupByClause(temp, list, example.groupByList, example.mustTableAlias, example.dialect);
        String groupByCaluse = temp.toString();
        if (StringUtils.isNotBlank(groupByCaluse)) {
            sql.append(" group by ").append(groupByCaluse);
            List<ICondition> c = example.getHavingList();
            if (c != null) {
                sql.append(" having ");
                for (ICondition condition : c) {
                    condition.toSql(sql, list, example.mustTableAlias, example.dialect);
                }
            }
        }

        getWindowClause(sql, list, example);

        temp.delete(0, temp.length());
        getOrderByClause(temp, list, example.orderByList, example.mustTableAlias, example.dialect);
        String orderByCaluse = temp.toString();
        if (StringUtils.isNotBlank(orderByCaluse)) {
            sql.append(" order by ").append(orderByCaluse);
        }

        if (CollectionUtils.isNotEmpty(example.unions)) {
            sql.insert(0, "(").append(')');
            for (UnionBuilder union : example.unions) {
                union.buildSql(sql, list, example.dialect);
            }
        }

        if (offsetLimit && example.getOffsetLimit() != null) {
            sql = buildLimitOffsetSql(sql, example.dialect, example.getOffsetLimit());
        }
        example.sqlFlag.toSql(SQLFlag.Position.END, sql, list);
        sqlContext.setSql(sql.toString());

        if (opt == SelectOpt.page) {
            StringBuilder countSql = new StringBuilder();
            getSelectClause(countSql, null, example, SelectOpt.count);
            if (StringUtils.isNotBlank(fromClause)) {
                countSql.append(" from ").append(fromClause);
            }
            if (StringUtils.isNotBlank(whereClause)) {
                countSql.append(" where ").append(whereClause);
            }
            example.sqlFlag.toSql(SQLFlag.Position.START, sql, list);
            sqlContext.setCountSql(countSql.toString());
            sqlContext.setListSql(sqlContext.getSql());
            sqlContext.setSql(null);
        }
        return sqlContext;
    }

    protected static void getWithClause(StringBuilder sb, List<SQLParameter> parameters, Example example) {
        WithBuilder withBuilder = example.withBuilder;
        if (withBuilder != null) {
            withBuilder.toSql(sb, parameters, new AtomicBoolean(false), example.dialect);
        }
    }

    protected static void getWindowClause(StringBuilder sb, List<SQLParameter> parameters, Example example) {
        WindowBuilder windowBuilder = example.windowBuilder;
        if (windowBuilder != null) {
            windowBuilder.toSql(sb, parameters, example.mustTableAlias, example.dialect);
        }
    }

    protected static void getSelectClause(StringBuilder sb, List<SQLParameter> list, Example example, SelectOpt opt) {
        sb.append("select ");
        example.sqlFlag.toSql(SQLFlag.Position.AFTER_SELECT, sb, list);
        boolean stop = false;
        if (opt != null) {
            switch (opt) {
                case count:
                    if (example.getCountColumn() != null) {
                        getCountColumn(sb, list, example);
                    } else {
                        sb.append("count(*)");
                    }
                    stop = true;
                    break;
                case exists:
                    sb.append("1");
                    stop = true;
                    break;
                default:
                    break;
            }
        }
        if (!stop) {
            List<SimpleField> selectColumns = example.getSelectColumns();
            if (CollectionUtils.isNotEmpty(selectColumns) || StringUtils.isNotBlank(example.select)) {
                if (example.isDistinct()) {
                    sb.append("distinct ");
                }
                if (CollectionUtils.isNotEmpty(selectColumns)) {
                    getColumns(sb, list, example);
                }
                if (StringUtils.isNotBlank(example.select)) {
                    sb.append(CollectionUtils.isNotEmpty(selectColumns) ? "," : "").append(SimpleSQLTemplate.unwrapper(example.dialect, example.select));
                }
                stop = true;
            }
        }
        if (!stop) {
            sb.append("*");
        }
        example.sqlFlag.toSql(SQLFlag.Position.AFTER_PROJECTION, sb, list);
    }

    /**
     * @param sb
     * @param list
     * @param example
     */
    public static void getFromClause(StringBuilder sb, List<SQLParameter> list, Example example) {
        boolean flag = false;
        if (CollectionUtils.isEmpty(example.fromTables) && CollectionUtils.isEmpty(example.fromConditions)) {
            for (ICondition condition : example.whereConditions) {//从条件中提取simpleTable信息
                SimpleTable simpleTable = condition.getTable();
                if (simpleTable != null) {
                    example.fromTables.add(simpleTable);
                    break;
                }
            }
        }
        example.hasMustTableAlias();
        for (SimpleTable simpleTable : example.fromTables) {
            if (flag) {
                sb.append(',');
            }
            simpleTable.toSql(sb, list, example.dialect);
            flag = true;
        }
        if (CollectionUtils.isNotEmpty(example.fromConditions)) {
            for (ICondition condition : example.fromConditions) {
                condition.toSql(sb, list, example.mustTableAlias, example.dialect);
            }
        }
    }

    public static void setDynamicTableName(Example example, String dynamicTableName) {
        if (StringUtils.isNotBlank(dynamicTableName)) {
            for (SimpleTable simpleTable : example.fromTables) {
                simpleTable.setTableName(dynamicTableName);
                break;
            }
        }
    }

    public static String getFromTableName(Example example) {
        String tableName = "";
        for (SimpleTable simpleTable : example.fromTables) {
            tableName = simpleTable.getTableName();
            if (example.dialect != null) {
                tableName = example.dialect.wrapKeyword(tableName);
            }
            if (StringUtils.isNotBlank(simpleTable.getTableAlias())) {
                tableName = tableName + " as " + simpleTable.getTableAlias();
            }
            break;
        }
        return tableName;
    }

    public static void getSQLContextWhereClause(StringBuilder sb, List<SQLParameter> parameters, Example example) {
        getSQLContextWhereClause(sb, parameters, example.whereConditions, example.mustTableAlias, example.dialect, example.whereWrapper);
    }

    public static void getSQLContextWhereClause(StringBuilder sb, List<SQLParameter> parameters, List<ICondition> whereConditions,
                                                AtomicBoolean mustTableAlias, DBDialect dialect, boolean whereWrapper) {
        StringBuilder temp = new StringBuilder();
        if (CollectionUtils.isNotEmpty(whereConditions)) {
            for (ICondition condition : whereConditions) {
                condition.toSql(temp, parameters, mustTableAlias, dialect);
            }
        }
        String content = temp.toString().trim();
        if (StringUtils.startsWithIgnoreCase(content, "and")) {
            content = content.substring(content.indexOf("and") + 3);
        } else if (StringUtils.startsWithIgnoreCase(content, "or")) {
            content = content.substring(content.indexOf("or") + 2);
        }//排除开头的and或or
        if (whereWrapper) {
            content = SimpleSQLTemplate.unwrapper(dialect, content);
        }
        sb.append(content);
    }

    public static void getGroupByClause(StringBuilder sb, List<SQLParameter> list, List<SimpleField> groupByList, AtomicBoolean mustTableAlias, DBDialect dialect) {
        if (CollectionUtils.isNotEmpty(groupByList)) {
            boolean f = false;
            for (SimpleField field : groupByList) {
                String name = field.getName();
                if (StringUtils.isNotBlank(name)) {
                    if (f) {
                        sb.append(',');
                    }
                    f = true;
                    field.toConditionSql(sb, list, mustTableAlias, true, dialect);
                }
            }
        }
    }

    public static void getOrderByClause(StringBuilder sb, List<SQLParameter> list, List<OrderField> orderByList, AtomicBoolean mustTableAlias, DBDialect dialect) {
        if (CollectionUtils.isNotEmpty(orderByList)) {
            boolean f = false;
            for (OrderField field : orderByList) {
                if (f) {
                    sb.append(',');
                }
                f = true;
                field.toConditionSql(sb, list, mustTableAlias, true, dialect);
            }
        }
    }


    public static StringBuilder buildLimitOffsetSql(StringBuilder sql, DBDialect dialect, OffsetLimit offsetLimit) {
        if (dialect == null) {
            throw new RuntimeException(" dialect is null");
        }
        return dialect.sqlPageList(sql, offsetLimit.getOffset(), offsetLimit.getLimit());
    }
}
